package com.test.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.test.objects.Author;
import com.test.objects.Category;
import com.test.objects.MyPageException;
import com.test.objects.Post;
import com.test.objects.PostClass;

@SuppressWarnings("unchecked")
public class PostDAOImpl extends MyPageDAOBase implements PostDAO {
	private static final String PROC_GET_ALL_POSTS = "sp_get_all_posts";
	private static final String PROC_GET_ALL_POSTS_LIMITED = "sp_get_all_posts_limited";
	private static final String PROC_GET_ALL_POSTS_LIMITED_BY_CLASS = "sp_get_all_posts_limited_by_class";
	private static final String PROC_GET_POST_COUNT = "sp_get_post_count";
	private static final String PROC_GET_POST_COUNT_BY_CLASS = "sp_get_post_count_by_class";
	//private static final String PROC_GET_POST = "sp_get_post";
	private static final String PROC_DELETE_POST = "sp_delete_post";
	private static final String PROC_DELETE_ALL_POSTS = "sp_delete_all_posts";
	private static final String PROC_SAVE_POST = "sp_save_post";
	
	public Post[] getAllPosts(int maxRow) {
		SqlParameterSource in = new MapSqlParameterSource().
				addValue("in_maxrow", maxRow);
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this.dataSource).
				withProcedureName(PROC_GET_ALL_POSTS).
				returningResultSet("posts", 
					new ParameterizedRowMapper<Post>() {
					public Post mapRow(ResultSet rs, int rowNum)
	                						throws SQLException {
						Post post = new Post();
						Category cat = new Category();
						Author author = new Author();
						post.setId(rs.getInt("post_id"));
						cat.setId(rs.getInt("cat_id"));
						cat.setName(rs.getString("cat_name"));
						post.setCategory(cat);
						author.setId(rs.getInt("author_id"));
						author.setFullname(rs.getString("author_fname"));
						post.setAuthor(author);
						post.setTitle(rs.getString("title"));
						post.setSummary(rs.getString("summary"));
						post.setImageUrl(rs.getString("imageUrl"));
						post.setContent("");
						post.setDateTime(new Date(rs.getTimestamp("date").getTime()));
						return post;
					}
				});
			
		List<Post> posts = (List<Post>)jdbcCall.execute(in).get("posts");
		return posts.toArray(new Post[] {});
	}

	public Post[] getAllPosts(int startRow, int numRow) {
		SqlParameterSource in = new MapSqlParameterSource().
				addValue("in_startrow", startRow).
				addValue("in_numrow", numRow);
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this.dataSource).
				withProcedureName(PROC_GET_ALL_POSTS_LIMITED).
				returningResultSet("posts", 
					new ParameterizedRowMapper<Post>() {
					public Post mapRow(ResultSet rs, int rowNum)
	                						throws SQLException {
						Post post = new Post();
						Category cat = new Category();
						Author author = new Author();
						post.setId(rs.getInt("post_id"));
						cat.setId(rs.getInt("cat_id"));
						cat.setName(rs.getString("cat_name"));
						post.setCategory(cat);
						author.setId(rs.getInt("author_id"));
						author.setFullname(rs.getString("author_fname"));
						post.setAuthor(author);
						post.setTitle(rs.getString("title"));
						post.setSummary(rs.getString("summary"));
						post.setImageUrl(rs.getString("imageUrl"));
						post.setContent("");
						post.setDateTime(new Date(rs.getTimestamp("date").getTime()));
						return post;
					}
				});
			
		List<Post> posts = (List<Post>)jdbcCall.execute(in).get("posts");
		return posts.toArray(new Post[] {});
	}

	public Post[] getAllPosts(PostClass pc, int id, int startRow, int numRow) {
		MapSqlParameterSource in = new MapSqlParameterSource();
		if (pc == PostClass.category){
			in.addValue("in_class_id", 1);
		}else if (pc == PostClass.author){
			in.addValue("in_class_id", 2);
		}
		in.addValue("in_id", id).
		   addValue("in_startrow", startRow).
		   addValue("in_numrow", numRow);
		SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this.dataSource).
				withProcedureName(PROC_GET_ALL_POSTS_LIMITED_BY_CLASS).
				returningResultSet("posts", 
					new ParameterizedRowMapper<Post>() {
					public Post mapRow(ResultSet rs, int rowNum)
	                						throws SQLException {
						Post post = new Post();
						Category cat = new Category();
						Author author = new Author();
						post.setId(rs.getInt("post_id"));
						cat.setId(rs.getInt("cat_id"));
						cat.setName(rs.getString("cat_name"));
						post.setCategory(cat);
						author.setId(rs.getInt("author_id"));
						author.setFullname(rs.getString("author_fname"));
						post.setAuthor(author);
						post.setTitle(rs.getString("title"));
						post.setSummary(rs.getString("summary"));
						post.setImageUrl(rs.getString("imageUrl"));
						post.setContent("");
						post.setDateTime(new Date(rs.getTimestamp("date").getTime()));
						return post;
					}
				});
		jdbcCall.setReturnValueRequired(false);	
		List<Post> posts = (List<Post>)jdbcCall.execute(in).get("posts");
		return posts.toArray(new Post[] {});
	}
	
	@SuppressWarnings("deprecation")
	public Post getPost(int pId) throws MyPageException{
		String sql = "SELECT post.Id AS post_id, " + 
					"category.Id AS cat_id, category.cat_name, " + 
					"author.Id AS author_id, author.fullname AS author_fname, " +
					"post.title, post.summary, post.imageUrl, post.content, post.`date` " + 
					"FROM post, category, author " +
					"WHERE post.catId = category.Id AND " +
					"post.authorId = author.Id AND " +
					"post.Id = '" + pId + "'";
		Post post = null;
		try{
			post = new SimpleJdbcTemplate(this.dataSource).
				queryForObject(sql, new ParameterizedRowMapper<Post>() {
					public Post mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Post post = new Post();
						Category cat = new Category();
						Author author = new Author();
						post.setId(rs.getInt("post_id"));
						cat.setId(rs.getInt("cat_id"));
						cat.setName(rs.getString("cat_name"));
						post.setCategory(cat);
						author.setId(rs.getInt("author_id"));
						author.setFullname(rs.getString("author_fname"));
						post.setAuthor(author);
						post.setTitle(rs.getString("title"));
						post.setSummary(rs.getString("summary"));
						post.setImageUrl(rs.getString("imageUrl"));
						post.setContent(rs.getString("content"));
						post.setDateTime(new Date(rs.getTimestamp("date").getTime()));
						return post;
					}});
			return post;
		}catch(EmptyResultDataAccessException erdaEx){
			String msg = "the post is corresponding to id ['" + pId + "'] does not exist in database";
			System.err.println("[mypage-post]" + msg);
			throw new MyPageException(msg);
		}
	}

	public int getNumPost(){
		Map<String, Object> out = executeSP(PROC_GET_POST_COUNT);
		return out.get("out_post_count") != null ? 
				(Integer)out.get("out_post_count") : 0;
	}
	
	public int getNumPost(PostClass pc, int id){
		MapSqlParameterSource in = new MapSqlParameterSource();
		if (pc == PostClass.category){
			in.addValue("in_class_id", 1).addValue("in_id", id);
		}else if (pc == PostClass.author){
			in.addValue("in_class_id", 2).addValue("in_id", id);
		}
		Map<String, Object> out = executeSP(
				PROC_GET_POST_COUNT_BY_CLASS, in);
		return out.get("out_post_count") != null ? 
				(Integer)out.get("out_post_count") : 0;
	}
	
	public int deletePost(int pId) {
		SqlParameterSource in = new MapSqlParameterSource().
								addValue("in_post_id", pId); 
		Map<String, Object> out = executeSP(PROC_DELETE_POST, in);
		return out.get("out_row_effect") != null ? 
				(Integer)out.get("out_row_effect") : 0;
	}

	public int deleteAllPosts() {
		Map<String, Object> out = executeSP(PROC_DELETE_ALL_POSTS);
		return out.get("#update-count-1") != null ? 
				(Integer)out.get("#update-count-1") : 0;
	}
	
	@SuppressWarnings("deprecation")
	public Category getCategory(int catId){
		String sql = "SELECT * " +
					"FROM category " +
					"WHERE Id='" + catId + "'";
		Category category = null;
		try {
			category = new SimpleJdbcTemplate(this.dataSource).
				queryForObject(sql, new ParameterizedRowMapper<Category>() {
					public Category mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Category cat = new Category();
						cat.setId(rs.getInt("Id"));
						cat.setName(rs.getString("cat_name"));
						return cat;
					}});
			return category;
		}catch(EmptyResultDataAccessException erdaEx){
			return null;
		}
	}
	
	@SuppressWarnings("deprecation")
	public Category[] getCategories(){
		String sql = "SELECT * FROM category";
		List<Category> cats = new SimpleJdbcTemplate(this.dataSource).
			query(sql, new ParameterizedRowMapper<Category>() {
				public Category mapRow(ResultSet rs, int rowNum)
						throws SQLException {
					Category cat = new Category();
					cat.setId(rs.getInt("Id"));
					cat.setName(rs.getString("cat_name"));
					return cat;
				}});
		return cats.toArray(new Category[] {});
	}
	
	@SuppressWarnings("deprecation")
	public Author getAuthor(int authorId){
		String sql = "SELECT * " +
				"FROM author " +
				"WHERE Id='" + authorId + "'";
		Author author = null;
		try {
			author = new SimpleJdbcTemplate(this.dataSource).
				queryForObject(sql, new ParameterizedRowMapper<Author>() {
					public Author mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Author aut = new Author();
						aut.setId(rs.getInt("Id"));
						aut.setFullname(rs.getString("fullname"));
						aut.setAccount(rs.getString("account"));
						aut.setPassword(rs.getString("password"));
						aut.setDob(new Date(rs.getTimestamp("dob").getTime()));
						aut.setAddress(rs.getString("address"));
						aut.setDescription(rs.getString("description"));
						return aut;
					}});
			return author;
		}catch(EmptyResultDataAccessException erdaEx){
			return null;
		}
	}
	
	public Author[] getAuthors(){
		return null;
	}
	
	public int savePost(Post post) throws MyPageException {
		SqlParameterSource in = new MapSqlParameterSource().
				addValue("in_cat_id", post.getCategory().getId()).
				addValue("in_author_id", post.getAuthor().getId()).
				addValue("in_title", post.getTitle()).
				addValue("in_summary", post.getSummary()).
				addValue("in_image_url", post.getImageUrl()).
				addValue("in_content", post.getContent()).
				addValue("in_date", post.getDateTime());
		Map<String, Object> out = executeSP(PROC_SAVE_POST, in);
		return out.get("out_post_id") != null ? 
				(Integer)out.get("out_post_id") : 0;
	}
}
